package controllers;
import java.sql.*;

public class MSAccessConnect {
    static Connection connection;
    static Statement statement;

    public static void main(String args[]){

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
            String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=data.mdb";
            connection = DriverManager.getConnection( database ,"",""); 

            //buildStatement();
            //executeQuery();

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("Error!");
        }
    }

    public static void buildStatement() throws SQLException {
        statement = connection.createStatement();
    }

    public static void executeQuery() throws SQLException {

        boolean foundResults = statement.execute("SELECT * FROM tblStaff  AS x WHERE City='Calgary'");
        if(foundResults){
            ResultSet set = statement.getResultSet();
            if(set!=null) displayResults(set);
        }else {
            connection.close();
        }
    }

    public static void displayResults(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columns=metaData.getColumnCount();
        String text="";

        while(rs.next()){
            for(int i=1;i<=columns;++i) {
                text+=""+metaData.getColumnName(i)+":\t";
                text+=rs.getString(i);
                //text+="</"+metaData.getColumnName(i)+">";
                text+="\n";
            }
            text+="\n";
        }

        System.out.println(text);

    }
}